package com.shui_shui.db;

import java.util.HashMap;
import java.util.Map;
import com.shui_shui.compass.Degree;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class myDB extends SQLiteOpenHelper {

	// Database Version
	private static final int DATABASE_VERSION = 1;
	// Database Name
	private static final String DATABASE_NAME = "myDB";

	private static final String EAST_INTR_TABLE = "CREATE TABLE \"EAST_INTR_TABLE\" ("
			+ "\"EAST_INTR_ID\"  INTEGER NOT NULL,"
			+ "\"EAST_INTR_N_COLUMN\"  INTEGER NOT NULL,"
			+ "\"EAST_INTR_NE_COLUMN\"  INTEGER NOT NULL,"
			+ "\"EAST_INTR_E_COLUMN\"  INTEGER NOT NULL,"
			+ "\"EAST_INTR_SE_COLUMN\"  INTEGER NOT NULL,"
			+ "\"EAST_INTR_S_COLUMN\"  INTEGER NOT NULL,"
			+ "\"EAST_INTR_SW_COLUMN\"  INTEGER NOT NULL,"
			+ "\"EAST_INTR_W_COLUMN\"  INTEGER NOT NULL,"
			+ "\"EAST_INTR_NW_COLUMN\"  INTEGER NOT NULL,"
			+ "\"EAST_INTR_IMG_COLUMN\"  TEXT,"
			+ "PRIMARY KEY (\"EAST_INTR_ID\" ASC),"
			+ "CONSTRAINT \"EAST_INTERACTION_N\" FOREIGN KEY (\"EAST_INTR_N_COLUMN\") REFERENCES \"INTERACTION_TABLE\" (\"INTERACTION_ID\"),"
			+ "CONSTRAINT \"EAST_INTERACTION_NE\" FOREIGN KEY (\"EAST_INTR_NE_COLUMN\") REFERENCES \"INTERACTION_TABLE\" (\"INTERACTION_ID\"),"
			+ "CONSTRAINT \"EAST_INTERACTION_E\" FOREIGN KEY (\"EAST_INTR_E_COLUMN\") REFERENCES \"INTERACTION_TABLE\" (\"INTERACTION_ID\"),"
			+ "CONSTRAINT \"EAST_INTERACTION_SE\" FOREIGN KEY (\"EAST_INTR_SE_COLUMN\") REFERENCES \"INTERACTION_TABLE\" (\"INTERACTION_ID\"),"
			+ "CONSTRAINT \"EAST_INTERACTION_S\" FOREIGN KEY (\"EAST_INTR_S_COLUMN\") REFERENCES \"INTERACTION_TABLE\" (\"INTERACTION_ID\"),"
			+ "CONSTRAINT \"EAST_INTERACTION_SW\" FOREIGN KEY (\"EAST_INTR_SW_COLUMN\") REFERENCES \"INTERACTION_TABLE\" (\"INTERACTION_ID\"),"
			+ "CONSTRAINT \"EAST_INTERACTION_W\" FOREIGN KEY (\"EAST_INTR_W_COLUMN\") REFERENCES \"INTERACTION_TABLE\" (\"INTERACTION_ID\"),"
			+ "CONSTRAINT \"EAST_INTERACTION_NW\" FOREIGN KEY (\"EAST_INTR_NW_COLUMN\") REFERENCES \"INTERACTION_TABLE\" (\"INTERACTION_ID\")"
			+ ");";

	private static final String FENGSHUI_BASE_TABLE = "CREATE TABLE \"FENGSHUI_BASE_TABLE\" ("
			+ "\"FENGSHUI_BASE_ID\"  INTEGER NOT NULL,"
			+ "\"FENGSHUI_BASE_TOPIC_COLUMN\"  TEXT NOT NULL,"
			+ "\"FENGSHUI_BASE_DES_COLUMN\"  TEXT NOT NULL,"
			+ "\"FENGSHUI_BASE_IMG_COLUMN\"  TEXT,"
			+ "PRIMARY KEY (\"FENGSHUI_BASE_ID\") );";

	private static final String GOODEAST_TABLE = "CREATE TABLE \"GOODEAST_TABLE\" ("
			+ "\"GOODEAST_ID\"  INTEGER NOT NULL,"
			+ "\"HOMEEAST_INTR_ID\"  INTEGER NOT NULL,"
			+ "\"GOODEAST_HUMAN_CODE_COLUMN\"  TEXT NOT NULL,"
			+ "\"GOODEAST_ELEMENT_COLUMN\"  TEXT NOT NULL,"
			+ "\"GOODEAST_ZODIAC_COLUMN\"  TEXT NOT NULL,"
			+ "\"GOODEAST_IMGEAST_COLUMN\"  TEXT,"
			+ "PRIMARY KEY (\"GOODEAST_ID\" ASC),"
			+ "CONSTRAINT \"GOODEAST_INTERACTION\" FOREIGN KEY (\"HOMEEAST_INTR_ID\") REFERENCES \"EAST_INTR_TABLE\" (\"EAST_INTR_ID\")"
			+ ");";

	private static final String GUIDE_TABLE = "CREATE TABLE \"GUIDE_TABLE\" ("
			+ "\"GUIDE_ID\"  INTEGER NOT NULL,"
			+ "\"GUIDE_TOPIC_COLUMN\"  TEXT NOT NULL,"
			+ "\"GUIDE_DES_COLUMN\"  TEXT NOT NULL,"
			+ "\"GUIDE_IMG_COLUMN\"  TEXT," + "PRIMARY KEY (\"GUIDE_ID\")"
			+ ");";

	private static final String HOMEEAST_TABLE = "CREATE TABLE \"HOMEEAST_TABLE\" ("
			+ "\"HOMEEAST_ID\"  INTEGER NOT NULL, "
			+ "\"HOMEEAST_INTR_ID\"  INTEGER NOT NULL,"
			+ "\"HOMEEAST_EAST_COLUMN\"  TEXT NOT NULL,"
			+ "\"HOMEEAST_MOUNTEAST_COLUMN\"  TEXT NOT NULL,"
			+ "\"HOMEEAST_MOUNT_COLUMN\"  TEXT NOT NULL, "
			+ "\"HOMEEAST_STARTEAST_COLUMN\"  INTEGER NOT NULL,"
			+ "\"HOMEEAST_ENDEAST_COLUMN\"  INTEGER NOT NULL, "
			+ "\"HOMEEAST_IMGEAST_COLUMN\"  TEXT,"
			+ "PRIMARY KEY (\"HOMEEAST_ID\" ASC),"
			+ "CONSTRAINT \"HOMEEAST_INTERACTION\" FOREIGN KEY (\"HOMEEAST_INTR_ID\") REFERENCES \"EAST_INTR_TABLE\" (\"EAST_INTR_ID\")"
			+ ");";

	private static final String INTERACTION_TABLE = "CREATE TABLE \"INTERACTION_TABLE\" ("
			+ "\"INTERACTION_ID\"  INTEGER NOT NULL, "
			+ "\"INTERACTION_TYPE_COLUMN\"  TEXT NOT NULL,"
			+ "\"INTERACTION_COLUMN\"  TEXT NOT NULL, "
			+ "\"INTERACTION_EFFECT_COLUMN\"  TEXT NOT NULL,"
			+ "\"INTERACTION_GUIDE_COLUMN\"  TEXT NOT NULL, "
			+ "\"INTERACTION_IMG_COLUMN\"  TEXT, "
			+ "PRIMARY KEY (\"INTERACTION_ID\" ASC)" + ");";

	private static final String LOCATION_BASE_TABLE = "CREATE TABLE \"LOCATION_BASE_TABLE\" ("
			+ "\"LOCATION_BASE_ID\"  INTEGER NOT NULL,"
			+ "\"LOCATION_BASE_TOPIC_COLUMN\"  TEXT NOT NULL,"
			+ "\"LOCATION_BASE_DES_COLUMN\"  TEXT NOT NULL,"
			+ "\"LOCATION_BASE_IMG_COLUMN\"  TEXT,"
			+ "PRIMARY KEY (\"LOCATION_BASE_ID\")" + ");";

	private static final String REVISION_TABLE = "CREATE TABLE \"REVISION_TABLE\" ("
			+ "\"REVISION_ID\"  INTEGER NOT NULL, "
			+ "\"REVISION_TOPIC_COLUMN\"  TEXT NOT NULL,"
			+ "\"REVISION_DES_COLUMN\"  TEXT NOT NULL, "
			+ "\"REVISION_IMG_COLUMN\"  TEXT, "
			+ "PRIMARY KEY (\"REVISION_ID\") " + ");";

	public myDB(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(EAST_INTR_TABLE);
		db.execSQL(FENGSHUI_BASE_TABLE);
		db.execSQL(GOODEAST_TABLE);
		db.execSQL(GUIDE_TABLE);
		db.execSQL(HOMEEAST_TABLE);
		db.execSQL(INTERACTION_TABLE);
		db.execSQL(LOCATION_BASE_TABLE);
		db.execSQL(REVISION_TABLE);

		Log.d("CREATE TABLE", "Create Table Successfully.");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

	public void selectDATABASE_REVISION_TABLE(int degree, Degree degreeClass) {

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db
				.rawQuery(
						"SELECT HOMEEAST_INTR_ID,HOMEEAST_MOUNT_COLUMN,HOMEEAST_IMGEAST_COLUMN FROM HOMEEAST_TABLE WHERE "
								+ degree
								+ " >= HOMEEAST_STARTEAST_COLUMN AND "
								+ degree + " <= HOMEEAST_ENDEAST_COLUMN", null);

		if (c != null) {
			if (c.moveToFirst()) {
				do {
					String HOMEEAST_MOUNT_COLUMN = c.getString(c
							.getColumnIndex("HOMEEAST_MOUNT_COLUMN"));
					String HOMEEAST_IMGEAST_COLUMN = c.getString(c
							.getColumnIndex("HOMEEAST_IMGEAST_COLUMN"));
					int HOMEEAST_INTR_ID = c.getInt(c
							.getColumnIndex("HOMEEAST_INTR_ID"));
					degreeClass.setMountDegree(HOMEEAST_MOUNT_COLUMN);
					degreeClass.setIdDegree(HOMEEAST_INTR_ID);
					degreeClass.setImageHome(HOMEEAST_IMGEAST_COLUMN);
				} while (c.moveToNext());
			}
		}

	}

	public String[] selectDATABASE_FENGSHUI_BASE_TABLE(int id) {
		try {
			String arrData[] = null;
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery(
					"SELECT * FROM FENGSHUI_BASE_TABLE WHERE FENGSHUI_BASE_ID = "
							+ id, null);

			if (c != null) {
				if (c.moveToFirst()) {
					arrData = new String[4];
					do {
						arrData[0] = c.getString(0);
						arrData[1] = c.getString(1);
						arrData[2] = c.getString(2);
						arrData[3] = c.getString(3);
					} while (c.moveToNext());
				}
			}
			c.close();
			return arrData;
		} catch (Exception e) {
			return null;
		}
	}

	public int[] selectDATABASE_EAST_INTR_TABLE_TABLE(int id) {
		try {
			int arrData[] = null;
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery(
					"SELECT * FROM EAST_INTR_TABLE WHERE EAST_INTR_ID = " + id,
					null);
			if (c != null) {
				if (c.moveToFirst()) {
					arrData = new int[8];
					do {
						arrData[0] = c.getInt(0);
						arrData[1] = c.getInt(1);
						arrData[2] = c.getInt(2);
						arrData[3] = c.getInt(3);
						arrData[4] = c.getInt(4);
						arrData[5] = c.getInt(5);
						arrData[6] = c.getInt(6);
						arrData[7] = c.getInt(7);
					} while (c.moveToNext());
				}
			}
			c.close();
			return arrData;
		} catch (Exception e) {
			return null;
		}
	}

	public String[] selectDATABASE_LOCATION_BASE_TABLE(int id) {
		try {
			String arrData[] = null;
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery("SELECT * FROM LOCATION_BASE_TABLE WHERE LOCATION_BASE_ID = "+ id, null);

			if (c != null) {
				if (c.moveToFirst()) {
					arrData = new String[4];
					do {
						arrData[0] = c.getString(0);
						arrData[1] = c.getString(1);
						arrData[2] = c.getString(2);
						arrData[3] = c.getString(3);
					} while (c.moveToNext());
				}
			}
			c.close();
			return arrData;
		} catch (Exception e) {
			return null;
		}
	}

	public String[] selectDATABASE_GUIDE_TABLE(int id) {
		try {
			String arrData[] = null;
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery("SELECT * FROM GUIDE_TABLE WHERE GUIDE_ID = "+ id, null);

			if (c != null) {
				if (c.moveToFirst()) {
					arrData = new String[4];
					do {
						arrData[0] = c.getString(0);
						arrData[1] = c.getString(1);
						arrData[2] = c.getString(2);
						arrData[3] = c.getString(3);
					} while (c.moveToNext());
				}
			}
			c.close();
			return arrData;
		} catch (Exception e) {
			return null;
		}
	}

	public String[] selectDATABASE_REVISION_TABLE(int id) {
		try {
			String arrData[] = null;
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery("SELECT * FROM REVISION_TABLE WHERE REVISION_ID = "+ id, null);

			if (c != null) {
				if (c.moveToFirst()) {
					arrData = new String[4];
					do {
						arrData[0] = c.getString(0);
						arrData[1] = c.getString(1);
						arrData[2] = c.getString(2);
						arrData[3] = c.getString(3);
					} while (c.moveToNext());
				}
			}
			c.close();
			return arrData;
		} catch (Exception e) {
			return null;
		}
	}

	public String[] selectGOODEAST_TABLE(int id) {
		try {
			String arrData[] = null;
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery(
					"SELECT * FROM GOODEAST_TABLE WHERE GOODEAST_HUMAN_CODE_COLUMN = " + id,
					null);

			if (c != null) {
				if (c.moveToFirst()) {
					arrData = new String[6];
					do {
						arrData[0] = c.getInt(0) + "";
						arrData[1] = c.getInt(1) + "";
						arrData[2] = c.getInt(2) + "";
						arrData[3] = c.getString(3);
						arrData[4] = c.getString(4);
						arrData[5] = c.getString(5);
					} while (c.moveToNext());
				}
			}
			c.close();
			return arrData;
		} catch (Exception e) {
			return null;
		}
	}

	public Map<String, String[]> selectINTERACTION_TABLE() {
		try {
			Map<String, String[]> arrayList = new HashMap<String, String[]>();
			String arrData[] = null;
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery("SELECT * FROM INTERACTION_TABLE", null);

			if (c != null) {
				if (c.moveToFirst()) {
					do {
						arrData = new String[5];
						arrData[0] = c.getString(0);
						arrData[1] = c.getString(1);
						arrData[2] = c.getString(2);
						arrData[3] = c.getString(3);
						arrData[4] = c.getString(4);
						arrayList.put(arrData[0], arrData);
					} while (c.moveToNext());
				}
			}
			c.close();
			return arrayList;
		} catch (Exception e) {
			return null;
		}
	}

	public String[] selectEAST_INTR_TABLE(int id) {
		try {
			if(id > 5){
				id--;
			}
			String arrData[] = null;
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery(
					"SELECT * FROM EAST_INTR_TABLE WHERE EAST_INTR_ID = " + id,
					null);

			if (c != null) {
				if (c.moveToFirst()) {
					arrData = new String[8];
					do {
						arrData[0] = c.getString(1) + "|N";
						arrData[1] = c.getString(2) + "|NE";
						arrData[2] = c.getString(3) + "|E";
						arrData[3] = c.getString(4) + "|SE";
						arrData[4] = c.getString(5) + "|S";
						arrData[5] = c.getString(6) + "|SW";
						arrData[6] = c.getString(7) + "|W";
						arrData[7] = c.getString(8) + "|NW";
					} while (c.moveToNext());
				}
			}
			c.close();
			return arrData;
		} catch (Exception e) {
			return null;
		}
	}

}
